﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace AccesoBD
{
    public class DBAccess {

        private String _connectionString = Properties.Settings.Default.CEC;

        public SqlConnection getConection()
        {
            return new SqlConnection(_connectionString);
        }
 
        public SqlDataReader ejecutarStoredProcedure(String sqlString)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = getConection();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sqlString;
 
            if (cmd.Connection.State == System.Data.ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }

            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
 
 
        public void ejecutarSQL(String sqlString, List<Parametros> parametros)
        {
            SqlCommand cmd;
            String nombre;

            cmd= new SqlCommand();
            cmd.Connection= getConection();
            foreach (Parametros objParametro in parametros)
            {
                nombre= objParametro.Nombre;
                cmd.Parameters.AddWithValue(nombre, objParametro.Dato);
                sqlString= sqlString.Replace(objParametro.Nombre, nombre);
            }
            cmd.CommandText = sqlString;

            if (cmd.Connection.State == System.Data.ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            cmd.ExecuteNonQuery(); 
            cmd.Connection.Close();
        }

        public String ejectutarSqlConScopeIdentity(String sqlString, List<Parametros> parametros)
        {
            SqlCommand cmd;
            String nombre;

            cmd = new SqlCommand();
            cmd.Connection = getConection();
            foreach (Parametros objParametro in parametros)
            {
                nombre = objParametro.Nombre;
                cmd.Parameters.AddWithValue(nombre, objParametro.Dato);
                sqlString = sqlString.Replace(objParametro.Nombre, nombre);
            }
            cmd.CommandText = sqlString;

            if (cmd.Connection.State == System.Data.ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            String insertedId = cmd.ExecuteScalar().ToString();
            cmd.Connection.Close();

            return insertedId;
        }
 
 
 
        public SqlDataReader ejecutarSelect(String sqlString, List<Parametros> parametros)
        {
            SqlCommand cmd;
            String nombre;
            SqlConnection conexion= getConection();
            cmd=  new SqlCommand();
            cmd.Connection = conexion;
 
            if (parametros != null) {
                foreach (Parametros objParametro in parametros)
                {
                    nombre = "@";
                    nombre += objParametro.Nombre;
                    cmd.Parameters.AddWithValue(nombre, objParametro.Dato);
                    sqlString = sqlString.Replace(objParametro.Nombre, nombre);
                }
            }
 
            cmd.CommandText = sqlString;
 
            if (cmd.Connection.State == System.Data.ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
 
            return cmd.ExecuteReader();
        }
 
    }
}
